﻿using System;
using System.Collections.Generic;
using System.Text;
using Wangzeran.CodeMaker.Core.DB;
using Wangzeran.CodeMaker.Core.Model;
using System.Data.SqlClient;
using System.Data;

namespace Wangzeran.CodeMaker.PlugIns.DB.SQLServer2000
{
    /// <summary>
    /// SQL Server 2000 数据库架构操作
    /// </summary>
    public class SQLServerSchema : SQLServerUtility, IDBSchema
    {

        #region IDBSchema 成员
        /// <summary>
        /// 获取数据库集合
        /// </summary>
        /// <returns></returns>
        public IDictionary<string, DatabaseModal> GetDatabases()
        {
            IDictionary<string, DatabaseModal> dicDatabases = new Dictionary<string, DatabaseModal>();

            string sql = String.Format("select name from sysdatabases");

            using (SqlConnection conn = (SqlConnection)GetConnection())
            {
                conn.Open();
                conn.ChangeDatabase("master");
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    DatabaseModal database = new DatabaseModal();
                    database.DatabaseName = reader[0].ToString();
                    dicDatabases.Add(database.DatabaseName, database);
                }
                reader.Close();
            }

            return dicDatabases;
        }
        /// <summary>
        /// 获取该数据库的所有表名
        /// </summary>
        /// <param name="databaseName">数据库名</param>
        /// <returns></returns>
        public IList<string> GetTableNames(string databaseName)
        {
            IList<string> lstTables = new List<string>();

            string sql = String.Format("select name from sysobjects where type='V' or type='U' order by name asc");
            using (SqlConnection conn = (SqlConnection)GetConnection())
            {
                conn.Open();
                conn.ChangeDatabase(databaseName);
                SqlCommand cmd = new SqlCommand(sql, conn);
                // 读取器
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    lstTables.Add(reader[0].ToString());
                }
                reader.Close();
            }

            return lstTables;
        }

        /// <summary>
        /// 获取该数据库的相关表的详细信息集合
        /// </summary>
        /// <param name="databaseName">数据库名</param>
        /// <param name="tableNames">表名集合</param>
        /// <returns></returns>
        public IDictionary<string, TableModal> GetTables(string databaseName, string[] tableNames)
        {
            IDictionary<string, TableModal> dicTables = new Dictionary<string, TableModal>();

            string sql = "select name from sysobjects where type='V' or type='U' order by name asc";
            using (SqlConnection conn = (SqlConnection)GetConnection())
            {
                conn.Open();
                conn.ChangeDatabase(databaseName);
                SqlCommand cmd = new SqlCommand(sql, conn);
                // 读取器
                SqlDataReader reader = cmd.ExecuteReader();
                string dicDescription = null;

                while (reader.Read())
                {
                    TableModal table = new TableModal();
                    table.TableName = reader[0].ToString();
                    table.DatabaseName = databaseName;
                    // 表描述
                    dicDescription = GetTableDescription(databaseName, table.TableName);
                    table.TableDescription = dicDescription != null
                        ? dicDescription.ToString() :
                        null;
                    dicTables.Add(table.TableName, table);
                }
                reader.Close();
            }

            return dicTables;
        }

        public IDictionary<string, TableModal> GetTables(string databaseName)
        {
            throw new Exception("The method or operation is not implemented.");
        }

        /// <summary>
        /// 获取该表的列集合
        /// </summary>
        /// <param name="databaseName">数据库名</param>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public IDictionary<string, ColumnModal> GetColumns(string databaseName, string tableName)
        {
            IDictionary<string, ColumnModal> lstColumns = new Dictionary<string, ColumnModal>();
            DataTable dt = new DataTable();

            string sql = String.Format("select top 0 * from {0}", tableName);
            using (SqlConnection conn = (SqlConnection)GetConnection())
            {
                conn.Open();
                conn.ChangeDatabase(databaseName);
                SqlDataAdapter sda = new SqlDataAdapter(sql, conn);

                sda.Fill(dt);
            }

            // 获取描述
            IDictionary<int, string> dicDescription = GetColumnDescription(databaseName, tableName);

            foreach (DataColumn col in dt.Columns)
            {
                ColumnModal column = new ColumnModal();
                column.ColumnName = col.ColumnName;
                column.ColumnType = col.DataType.ToString();
                column.TableName = tableName;
                // 获取列名描述
                column.Description = dicDescription.ContainsKey(col.Ordinal + 1)
                                        ? dicDescription[col.Ordinal + 1]
                                        : String.Empty;

                lstColumns.Add(column.ColumnName, column);
            }

            return lstColumns;

        }

        #endregion


        /// <summary>
        /// 获取表的描述
        /// </summary>
        /// <param name="databaseName">数据库名称</param>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        private string GetTableDescription(string databaseName, string tableName)
        {
            try
            {
                string tableDescription = null;

                string sql = String.Format("select value from dbo.sysproperties where id = (select id from sysobjects where name='{0}') ", tableName);
                using (SqlConnection conn = (SqlConnection)GetConnection())
                {
                    conn.Open();
                    conn.ChangeDatabase(databaseName);
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    SqlDataReader reader = cmd.ExecuteReader();
                    if (reader.Read())
                    {
                        tableDescription = reader["value"].ToString();
                    }
                    reader.Close();
                }

                return tableDescription;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 获取表的描述
        /// </summary>
        /// <param name="databaseName">数据库名称</param>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        private IDictionary<int, string> GetColumnDescription(string databaseName, string tableName)
        {
            try
            {
                IDictionary<int, string> dic = new Dictionary<int, string>();

                string sql = String.Format(@"declare @id bigint
select @id = id from sysobjects where name= '{0}'
select smallid, value from dbo.sysproperties where smallid in (select colid from dbo.syscolumns where id= @id) and id = @id", tableName);
                using (SqlConnection conn = (SqlConnection)GetConnection())
                {
                    conn.Open();
                    conn.ChangeDatabase(databaseName);
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    SqlDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        if (!dic.ContainsKey(Convert.ToInt32(reader["smallid"])))
                        {
                            dic.Add(Convert.ToInt32(reader["smallid"]), reader["value"].ToString());
                        }
                    }
                    reader.Close();
                }

                return dic;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}
